﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using EasyToHelp.Business;
using EasyToHelp.DBFactory;
using EasyToHelp.Entity;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

namespace EasyToHelp.Business
{
    public static class FormService
    {
        #region 查询表配置信息
        /// <summary>
        /// 查询配置信息
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public static List<TableManageField> GetTableManageFieldList(string TableName)
        {
            string sql = string.Format("SELECT 	*  FROM TableManageField WHERE CodeName='{0}'", TableName);
            return MySqlHelper.GetList<TableManageField>(sql);
        }
        public static List<TableManageField> GetTableManageFieldList()
        {
            return DBHelper.GetList<TableManageField>();
        }
        public static IEnumerable<TableManageField> GetTableManageField(string FieldType)
        {
            string sql = string.Format("SELECT 	*  FROM TableManageField WHERE EnglishName='{0}'", FieldType);
            return MySqlHelper.GetList<TableManageField>(sql);
        }
        #endregion

        #region 查询任意表数据
        /// <summary>
        /// 返回任意表数据
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="PkName">条件键name</param>
        /// <param name="PkValue">条件值</param>
        /// <returns></returns>
        public static DataTable GetArbitrarilyData(string TableName, string PkName, string PkValue)
        {
            if (string.IsNullOrWhiteSpace(TableName) || string.IsNullOrWhiteSpace(PkName) || string.IsNullOrWhiteSpace(PkValue))
            {
                return null;
            }
            string SqlQuery = string.Format("SELECT * FROM {0} WHERE {1}='{2}'", TableName, PkName, PkValue);
            return MySqlHelper.GetDataTable(SqlQuery);
        }
        #endregion

        #region 获取控件的数据源
        /// <summary>
        /// 获取控件的数据源
        /// </summary>
        /// <param name="SqlQuery"></param>
        /// <returns></returns>
        public static DataTable GetControlInfo(string SqlQuery)
        {
            return MySqlHelper.GetDataTable(SqlQuery);
        }
        #endregion

        #region 编辑数据
        public static object EditData(string datajson)
        {
            try
            {
                JObject outputObj = JObject.Parse(datajson);
                string TableName = outputObj["TableName"].ToString();
                string PkName = outputObj["PkName"].ToString();
                string PkValue = outputObj["PkValue"].ToString();
                string FkName = string.Empty;
                string FkValue = string.Empty;
                if (outputObj["FkName"] != null)
                {
                    FkName = outputObj["FkName"].ToString();
                }
                if (outputObj["FkName"] != null)
                {
                    FkValue = outputObj["FkValue"].ToString();
                }
                var list = GetTableInfo(outputObj["TableName"].ToString());
                string sql = string.Empty;
                DataTable dt = GetArbitrarilyData(TableName, FkName, FkValue);
                if (dt != null && dt.Rows.Count > 0)
                {
                    StringBuilder SqlQuery = new StringBuilder();
                    SqlQuery.Append("UPDATE  " + outputObj["TableName"].ToString() + "  SET ");
                    string columnString = string.Empty;
                    foreach (var item in list)
                    {
                        if (item.column == "Id")
                        {
                            columnString += null;
                        }
                        else
                        {
                            if (outputObj[item.column] != null)
                            {
                                columnString += item.column + "='" + outputObj[item.column].ToString() + "',";
                            }
                        }
                    }
                    SqlQuery.Append(columnString.TrimEnd(','));
                    SqlQuery.Append(" WHERE " + FkName + "=" + "'" + FkValue + "'");
                    sql = SqlQuery.ToString();
                }
                else
                {
                    StringBuilder SqlQuery = new StringBuilder();
                    SqlQuery.Append("insert into " + outputObj["TableName"].ToString());
                    SqlQuery.Append("(");
                    #region 获取对应表的列名
                    string columnString = string.Empty;
                    foreach (var item in list)
                    {
                        if (item.column == "Id")
                        {
                            columnString += null;
                        }
                        else if (outputObj[item.column] != null)
                        {
                            columnString += item.column + ",";
                        }
                    }
                    SqlQuery.Append(columnString.TrimEnd(','));
                    #endregion
                    SqlQuery.Append(")");
                    SqlQuery.Append(" VALUES(");
                    #region 获取对应表列的值
                    string vauleString = string.Empty;
                    foreach (var item in list)
                    {
                        if (item.column == "Id")
                        {
                            vauleString += null;
                        }
                        else if (outputObj[item.column] != null)
                        {
                            vauleString += "'" + outputObj[item.column].ToString() + "'" + ",";
                        }
                    }
                    SqlQuery.Append(vauleString.TrimEnd(','));
                    #endregion
                    SqlQuery.Append(")");
                    sql = SqlQuery.ToString();
                }
                if (MySqlHelper.Execute(sql) > 0)
                {
                    return ResultInfo.Success("编辑成功！");
                }
                return ResultInfo.Error("编辑失败！");
            }
            catch (Exception ex)
            {
                return ResultInfo.Fail("系统错误：" + ex.ToString());
            }
        }
        #endregion

        #region 获取表信息
        /// <summary>
        /// 获取表信息
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public static List<TableInfo> GetTableInfo(string TableName)
        {
            var sqlSyscolumns = string.Format(" SELECT  COLUMN_NAME as 'column' ,DATA_TYPE as 'type' ,COLUMN_TYPE as 'length' FROM information_schema.`COLUMNS` where TABLE_NAME like '{0}'", TableName);
            return MySqlHelper.GetList<TableInfo>(sqlSyscolumns);
        }
        #endregion

        #region 获取所有表单信息
        public static List<TableManageFormList> GetTableFormList()
        {
            return DBHelper.GetList<TableManageFormList>();
        }
        #endregion

        #region 同步数据操作
        public static object synTableManageField(string TableName)
        {
            if (GetTableManageFieldList(TableName).Count>0)
            {
                return ResultInfo.Error("非常遗憾，同步失败！原因：您已经手动添加过了！");
            }
            else
            {
                var Syscolumns = GetTableInfo(TableName);
                int index = 1;
                string Sql;
                try
                {
                    foreach (var item in Syscolumns)
                    {
                        Sql = string.Format("INSERT INTO TableManageField(ChineseName,EnglishName,Msg,TypeLength,PId,FieldType,CodeName,BusinessName,TextFieldName,VauleFieldName,SqlQuery,SortOrder)VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}')", item.column, item.column, "", item.type, 0, "text", TableName, TableName, "", "", "", ++index);
                        MySqlHelper.Execute(Sql);
                    }
                }
                catch (Exception ex)
                {
                    return ResultInfo.Fail("系统错误：" + ex.ToString());
                }
                return ResultInfo.Success("您贼幸运，已同步成功【" + (index - 1) + "条数据】！");
            }
        }
        #endregion

    }
}
